import psycopg2
import RouteGenerator, util
import os

username = ''
password = ''
dbname   = 'cyclesense'
host     = 'localhost'

def setConfig():
    global username, password, dbname, host
    username = RouteGenerator.username
    password = RouteGenerator.password
    dbname   = RouteGenerator.dbname
    host     = RouteGenerator.host
    
def markRouteRatingWithGID(gid, rating):
    global username,password,dbname,host
    if username == '' or password == '' or dbname == '' or host == '':
        setConfig()
    if rating < 1 or rating > 10:
        print "Bad rating...must be in between 1 and 10"
        return []
    conn = psycopg2.connect("dbname = '" + dbname + "' host = '" + host + "' user = " + username + " password = " + password)
    cur = conn.cursor()
    
    query = "SELECT gid, ranking, votes, tnode, fnode FROM la_roads WHERE gid = " + str(gid)
    cur.execute(query)
    edge2 = cur.fetchall()
    edge = edge2[0]
    tnode = edge[3]
    fnode = edge[4]
    totalVotes = edge[2] + 1
    #print totalVotes
    query = "UPDATE la_roads SET ranking = " + str(rating) + " WHERE gid = " + str(gid)
            #print newquery
    cur.execute(query)
    query = "UPDATE la_roads SET votes = " + str(totalVotes) + " WHERE gid = " + str(gid)
            #print newquery
    cur.execute(query)
    conn.commit()
    RouteGenerator.updateRanking(tnode, fnode, rating)
            #print newranking
    return 1

def markRouteRating(fullAddress, rating ):
    global username, password, dbname, host
    if username == '' or password == '' or dbname == '' or host == '':
        setConfig()
    try:
        address, streetName, roadType, zip = util.splitAddress(fullAddress)
    except Exception:
        print "Bad address format"
        return []
    if rating < 1 or rating > 10:
        print "Bad rating...must be in between 1 and 10"
        return []
    conn = psycopg2.connect("dbname = '" + dbname + "' host = '" + host + "' user = " + username + " password = " + password)
    cur = conn.cursor()
    if address == '':
        query = "SELECT gid, ranking, votes, tnode, fnode FROM la_roads WHERE fename = '" + streetName + "' AND fetype = '" + roadType + "'AND (zipl = '" + zip + "' OR zipr = '" + zip + "')"
    else:
        query = "SELECT gid, ranking, votes, tnode, fnode FROM la_roads WHERE fename = '" + streetName + "' AND fetype = '" + roadType + "' AND (" + address + " BETWEEN fraddl AND toaddl OR " + address + " BETWEEN toaddl AND fraddl) AND (zipl = '" + zip + "' OR zipr = '" + zip + "')"
    cur.execute(query)
    edges = cur.fetchall()
    
    if len(edges) == 1 or (address == '' and len(edges) > 0):
        for edge in edges:
            gid = edge[0]
            ranking = edge[1]
            votes = edge[2]
            tnode = edge[3]
            fnode = edge[4]
            
            #totalVotes = 1
            #newranking = rating
            totalVotes = votes + 1
            newranking = float(votes)/totalVotes * ranking + (1./totalVotes)*rating 
            
            newquery = "UPDATE la_roads SET ranking = " + str(newranking) + " WHERE gid = " + str(gid)
            #print newquery
            cur.execute(newquery)
            newquery = "UPDATE la_roads SET votes = " + str(totalVotes) + " WHERE gid = " + str(gid)
            #print newquery
            cur.execute(newquery)
            conn.commit()
            RouteGenerator.updateRanking(tnode, fnode, newranking)
            #print newranking
        return 1
    else:
        print len(edges)
        print "Failure...unknown address, or more than 1 address"
        return []

"*** if need to access unavailable for bikers...then put forCars as false***"
# mark route as available or unavailable
def markRouteUnavailability(address, streetName, roadType, zip, unavailable, forCars):
    global username, password, dbname, host

    conn = psycopg2.connect("dbname = '" + dbname + "' host = '" + host + "' user = " + username + " password = " + password)
    cur = conn.cursor()
    if forCars == True:
        if address == '':
            query = "SELECT gid, unavailableCars, tnode, fnode FROM la_roads WHERE fename = '" + streetName + "' AND fetype = '" + roadType + "'AND (zipl = '" + zip + "' OR zipr = '" + zip + "')"
        else:
            query = "SELECT gid, unavailableCars, tnode, fnode FROM la_roads WHERE fename = '" + streetName + "' AND fetype = '" + roadType + "' AND (" + address + " BETWEEN fraddl AND toaddl OR " + address + " BETWEEN fraddl AND fraddl) AND (zipl = '" + zip + "' OR zipr = '" + zip + "')"
    else:
        if address == '':
            query = "SELECT gid, unavailableBikes, tnode, fnode FROM la_roads WHERE fename = '" + streetName + "' AND fetype = '" + roadType + "' AND (zipl = '" + zip + "' OR zipr = '" + zip + "')"
        else:
            query = "SELECT gid, unavailableBikes, tnode, fnode FROM la_roads WHERE fename = '" + streetName + "' AND fetype = '" + roadType + "' AND (" + address + " BETWEEN fraddl AND toaddl OR " + address + " BETWEEN fraddl AND fraddl) AND (zipl = '" + zip + "' OR zipr = '" + zip + "')"
            
    cur.execute(query)
    edges = cur.fetchall()
    if len(edges) == 1 or (address == '' and len(edges) > 0):
        for edge in edges:
            gid         = edge[0]
            tnode       = edge[2]
            fnode       = edge[3]
            #unavailable = edge[1]
            #unavailable = unavailable + 1
            #print unavailable
            if forCars == True:
                newquery = "UPDATE la_roads SET unavailableCars = " + str(unavailable) + " WHERE gid = " + str(gid)
            else:
                newquery = "UPDATE la_roads SET unavailableBikes = " + str(unavailable) + " WHERE gid = " + str(gid)
    
            cur.execute(newquery)
            conn.commit()
            RouteGenerator.updateUnavailability(tnode, fnode, unavailable, forCars)
        return []
    else:
        print "Failure...unknown address"
        return []

def markRouteUnavailableCars(address, streetName, roadType, zip):
    return markRouteUnavailability(address, streetName, roadType, zip, 1, True)

def markRouteUnavailableBikes(address, streetName, roadType, zip):
    return markRouteUnavailability(address, streetName, roadType, zip, 1, False)

def markRouteAvailableCars(address, streetName, roadType, zip):
    return markRouteUnavailability(address, streetName, roadType, zip, 0, True)

def markRouteAvailableBikes(address, streetName, roadType, zip):
    return markRouteUnavailability(address, streetName, roadType, zip, 0, False)

"ONE OF THE MAIN FUNCTIONS"
def markRouteAvailability(fullAddress, modeOfT, unavailable):
    global username, password, dbname, host
    if username == '' or password == '' or dbname == '' or host == '':
        setConfig()
    try:
        address, streetName, roadType, zip = util.splitAddress(fullAddress)
    except Exception:
        print "Bad address format"
        return []
    
    if modeOfT == 'bike':
        if unavailable == False: # meaning the road is now available
            markRouteAvailableBikes(address, streetName, roadType, zip)
        else:
            markRouteUnavailableBikes(address, streetName, roadType, zip)
    elif modeOfT == 'car':
        if unavailable == False:
            markRouteAvailableCars(address, streetName, roadType, zip)
        else:
            markRouteUnavailableCars(address, streetName, roadType, zip)